
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spBulkOrdersGetListByOfficeID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [spBulkOrdersGetListByOfficeID]
GO

CREATE PROCEDURE spBulkOrdersGetListByOfficeID
	@OfficeID int,
	@OrderBy varchar(50),
	@OrderDirection varchar(5),
	@Page int,
	@PageSize int,
	@TotalRecords int output
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

CREATE TABLE #TempBulkOrders (
	RowNumber INT IDENTITY (1, 1) NOT NULL,
	BulkOrderID int	
)

DECLARE @sql nvarchar(2000)

-- insert primary keys into temp table
SET @sql =	N'INSERT INTO #TempBulkOrders ([BulkOrderID]) SELECT '
IF (@PageSize > 0)
	SET @sql = @sql + ' TOP ' + CAST(@Page*@PageSize as nvarchar) 

SET @sql = @sql + ' [BulkOrderID] FROM [BulkOrders] WHERE OfficeID=' +
		CAST(@OfficeID as nvarchar) + '  ORDER BY [' + @OrderBy + N'] ' + @OrderDirection

EXEC (@sql)

SELECT @TotalRecords = COUNT(*) FROM [BulkOrders] 
WHERE OfficeID = @OfficeID

SELECT
	[BulkOrders].[BulkOrderID],
	[OrderDate],
	[Type],
	[ProductCode],
	[Description],
	[Color],
	[StartDate],
	[TotalQtyOrdered],
	[TotalQtyCompleted],
	[TotalQtyInProgressBelow50P],
	[TotalQtyInProgressAbove50P],
	[EstimatedDate],
	[StockLastCheckedDate],
	[StockLastCheckedQty],
	[FOB],
	[CompletedDate]	
FROM
	#TempBulkOrders AS tblTemp JOIN [BulkOrders] ON
	tblTemp.BulkOrderID = [BulkOrders].BulkOrderID 	
WHERE (@PageSize = 0) OR (@PageSize > 0 AND (@Page - 1)*@PageSize < RowNumber AND RowNumber <= @Page*@PageSize)
ORDER BY RowNumber

DROP TABLE #TempBulkOrders

GO

	
 